package AccessDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Aluno;
import model.Responsavel;

public class ResponsavelDAO {

	private Connection con;

	public ResponsavelDAO() {
		super();
		con = ConnectionDB.getInstance().getConnection();
	}

	public void inserirResponsavel(Responsavel responsavel) throws SQLException {

		PreparedStatement stmt = con
				.prepareStatement("insert into Responsavel (cpfResp, nomeCompleto, nomeUsual, email, telResidencial, celular) values(?,?,?,?,?,?)");
		stmt.setString(1, responsavel.getCpf());
		stmt.setString(2, responsavel.getNomeCompleto());
		stmt.setString(3, responsavel.getNomeUsual());
		stmt.setString(4, responsavel.getEmail());
		stmt.setString(5, responsavel.getTel());
		stmt.setString(6, responsavel.getCelular());

		stmt.execute();
	}

	public void associarResponsavelAoAluno(String idResponsavel, String matAluno)
			throws SQLException {

		PreparedStatement stmt = con
				.prepareStatement("insert into AlunoResp (matAluno, cpfResp) values(?,?)");
		stmt.setString(1, matAluno);
		stmt.setString(2, idResponsavel);

		stmt.execute();

	}

	public Responsavel retornaResponsavel(String cpf) throws SQLException {
		PreparedStatement stmt = con
				.prepareStatement("select * from Responsavel where cpfResp = ?");
		stmt.setString(1, cpf);

		ResultSet rs = stmt.executeQuery();
		rs.next();
		return this.gerarResponsavel(rs);

	}

	public List<Aluno> retornaDependentes(String cpf) throws SQLException {
		List<Aluno> dependentes = new ArrayList<Aluno>();

		PreparedStatement stmt = con
				.prepareStatement("select Aluno.matricula, Aluno.idTurma, Aluno.nomeCompleto, Aluno.nomeUsual, Aluno.identidade, Aluno.email, Aluno.foto from Aluno inner join AlunoResp on Aluno.matricula = AlunoResp.matAluno where cpfResp = ?");
		stmt.setString(1, cpf);
		
		ResultSet rs = stmt.executeQuery();
		while(rs.next())
			dependentes.add(new AlunoDAO().gerarAluno(rs));
		
		return dependentes;
	}

	public Responsavel gerarResponsavel(ResultSet rs) throws SQLException {
		Responsavel r = new Responsavel();
		r.setCelular(rs.getString("celular"));
		r.setCpf(rs.getString("cpfResp"));
		r.setEmail(rs.getString("email"));
		r.setNomeCompleto(rs.getString("nomeCompleto"));
		r.setNomeUsual(rs.getString("nomeUsual"));
		r.setTel(rs.getString("telResidencial"));
		r.setDependentes(this.retornaDependentes(r.getCpf()));
		return r;

	}

}